package service;

import Req.Request;
import entity.*;
import util.DBUtil;
import util.GetsTablefieldsUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Vector;

public class PersonnelServiceImpl implements PersonnelService {
    @Override
    public TableDTO retrieveStudents(Request request, String tablesName) {
        String dept=request.getDeptsearchKey().trim();
        String objc=request.getObjcsearchKey().trim();
        String ach=request.getAchievementsearchKey().trim();
        StringBuilder sql = new StringBuilder();
        String tables=tablesName;
        sql.append("select * from "+tables);
        if(tablesName.equals("Personnel")&&!"".equals(ach) &&"".equals(dept)&&"".equals(objc)){
          sql.setLength(0);
          sql.append("SELECT p.*\n" +
                  "FROM personnel p\n" +
                  "JOIN projectpersonneltable ppt ON p.PersonnelID = ppt.PersonnelID\n" +
                  "JOIN proj pr ON ppt.ProjectID = pr.ProjectID\n" +
                  "JOIN projectawardform paf ON pr.ProjectID = paf.ProjectID\n" +
                  "WHERE paf.AchievementID = "+ach);
        }else if(tablesName.equals("Personnel")&&!"".equals(objc)&&"".equals(ach)&&"".equals(dept)){
            sql.setLength(0);
            sql.append("SELECT P.*" + "FROM "+tablesName+" P " +
                    "JOIN ProjectPersonnelTable PP ON P.PersonnelID = PP.PersonnelID " +
                    "WHERE PP.ProjectID = "+request.getObjcsearchKey()+";");
        }else {
            if(!"".equals(request.getDeptsearchKey().trim())){
                System.out.println(request.getDeptsearchKey());
                sql.append(" WHERE DepartmentID = "+request.getDeptsearchKey());
            }
            if(!"".equals(request.getObjcsearchKey().trim())) {
                System.out.println(request.getObjcsearchKey());
                sql.append(" WHERE ProjectID = "+request.getObjcsearchKey());
            }
            if(!"".equals(request.getAchievementsearchKey().trim())) {
                System.out.println(request.getAchievementsearchKey());
                sql.append(" WHERE AchievementID = "+request.getAchievementsearchKey());
            }
        }
        System.out.println("retrieveStudents:"+sql);
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        TableDTO retTableDTO = new TableDTO();
        List<Object[]> dataList = new ArrayList<>();
        try {
            connection = DBUtil.getConn();
            ps = connection.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                // 每一条记录
                Vector<String> personnels = GetsTablefieldsUtil.getAllcolumns(tables);
                int length=personnels.size();
                Object[] oneRecord = new Object[length];
                for (int i = 0; i < length; i++) {
                    oneRecord[i]=rs.getObject(personnels.get(i));
                }
                dataList.add(oneRecord);
            }
            // 将 List<Object[]> 转换为 Vector<Vector<Object>>
            Vector<Vector<Object>> data = new Vector<>();
            for (int i =0 ; i <dataList.size() ; i++) {
                Object[] row = dataList.get(i);
                Vector<Object> vectorRow = new Vector<>(Arrays.asList(row));
                data.add(vectorRow);
            }
            retTableDTO.setData(data);
            sql.setLength(0);
            sql.append("select count(*) from "+tables);
            ps = connection.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                int count = rs.getInt(1);
                retTableDTO.setTotalCount(count);
            }
            return retTableDTO;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePS(ps);
            DBUtil.closeConn(connection);
        }
        return null;
    }

    //添加是吧
//    拼接INSERT INTO achievement(AchievementID,Category,AchievementLevel,Grade) VALUES (?,?,?,?)
    public boolean add3(Achievement achievement,String tablesName) {
        StringBuilder sql=new StringBuilder();
        sql.append("INSERT INTO ");
        sql.append(tablesName);
        sql.append(" (AchievementID, Category, AchievementLevel, Grade) VALUES ( ?, ?, ?, ?)");
        Connection connection=null;
        PreparedStatement ps=null;
        try{
            connection=DBUtil.getConn();
            ps = connection.prepareStatement(sql.toString());
            ps.setInt(1, achievement.getAchievementID());
            ps.setString(2, achievement.getCategory());
            ps.setString(3, achievement.getAchievementLevel());

            ps.setInt(4, achievement.getGrade());
            System.out.println(sql);
            return ps.executeUpdate()==1;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePS(ps);
            DBUtil.closeConn(connection);
        }
        return false;
    }
//    @Override
    public boolean add2(Achievement achievement,String tablesName) {
        //准备工作：
        Vector<String> Fieldname=new Vector<>();
        Vector<String> DataType=new Vector<>();
        try (Connection connection = DBUtil.getConn()) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet columns = metaData.getColumns(null, null, tablesName, null);
            int i=0;
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                Fieldname.add(columnName);
                String columnType = columns.getString("TYPE_NAME");
                DataType.add(columnType);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        StringBuilder sql=new StringBuilder();
        sql.append("INSERT INTO ");
        sql.append(tablesName);
        sql.append("(");
        int length=Fieldname.size();
        for(int i=0;i<length;i++){
            if(i<length-1){
                sql.append(Fieldname.get(i)+",");
            }else {
                sql.append(Fieldname.get(i));
            }

        }
        sql.append(") VALUES (");
        for(int i=0;i<length;i++){
            if(i<length-1){
                sql.append("?,");
            }else {
                sql.append("?");
            }
        }
        sql.append(")");

        System.out.println("拼接"+sql);
        Connection connection=null;
        PreparedStatement ps=null;
        try{
            connection=DBUtil.getConn();
            ps = connection.prepareStatement(sql.toString());
            ps.setInt(1, achievement.getAchievementID());
            ps.setString(2, achievement.getCategory());
            ps.setString(3, achievement.getAchievementLevel());
            ps.setInt(4, achievement.getGrade());

            return ps.executeUpdate()==1;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePS(ps);
            DBUtil.closeConn(connection);
        }
        return false;
    }
    @Override
    public boolean add(Vector<Object> attributes,String tablesName) {
        //准备工作：
        Vector<String> Fieldname=new Vector<>();
        Vector<String> DataType=new Vector<>();
        try (Connection connection = DBUtil.getConn()) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet columns = metaData.getColumns(null, null, tablesName, null);
            int i=0;
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                Fieldname.add(columnName);
                String columnType = columns.getString("TYPE_NAME");
                DataType.add(columnType);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        StringBuilder sql=new StringBuilder();
        sql.append("INSERT INTO ");
        sql.append(tablesName);
        sql.append("(");
        int length=Fieldname.size();
        int s=0,j=0;
        if(tablesName.equals("projectpersonneltable")){
            s=1;
            j=1;
        }
        for(;s<length;s++){
            if(s<length-1){
                sql.append(Fieldname.get(s)+",");
            }else {
                sql.append(Fieldname.get(s));
            }

        }
        sql.append(") VALUES (");
        for(;j<length;j++){
            if(j<length-1){
                sql.append("?,");
            }else {
                sql.append("?");
            }
        }
        sql.append(")");
        System.out.println("拼接"+sql);
        Connection connection=null;
        PreparedStatement ps=null;

        try{
            connection=DBUtil.getConn();
            ps = connection.prepareStatement(sql.toString());
            int l=DataType.size();

            if(tablesName.equals("projectpersonneltable")){
                ps.setInt(1,(Integer) attributes.get(0));
                ps.setInt(2,(Integer) attributes.get(1));
                ps.setString(3,(String) attributes.get(2));
                ps.setDate(4,(Date) attributes.get(3));
                ps.setDate(5,(Date) attributes.get(4));
            }else {
                for (int i = 0; i < l ; i++) {
                    if(DataType.get(i).equals("INT")||DataType.get(i).equals("INT UNSIGNED")){
                        ps.setInt(i+1, (Integer) attributes.get(i));
                    }else if(DataType.get(i).equals("VARCHAR")){
                        ps.setString(i+1,(String)attributes.get(i));
                    } else if (DataType.get(i).equals("DATE")) {
                        ps.setDate(i+1,(Date)attributes.get(i));
                    } else if (DataType.get(i).equals("DECIMAL")) {
                        ps.setDouble(i+1,(Double)attributes.get(i));
                    }
                }
            }
            return ps.executeUpdate()==1;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePS(ps);
            DBUtil.closeConn(connection);
        }
        return false;
    }
    @Override
    public Vector<Object> getBy(int selectedStudentId, String tableName) {
        //准备工作：
        Vector<String> Fieldname=new Vector<>();
        Vector<String> DataType=new Vector<>();
        try (Connection connection = DBUtil.getConn()) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet columns = metaData.getColumns(null, null, tableName, null);
            int i=0;
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                Fieldname.add(columnName);
                String columnType = columns.getString("TYPE_NAME");
                DataType.add(columnType);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("selectedStudentId:"+selectedStudentId);
        for (int i = 0; i < Fieldname.size(); i++) {
            System.out.println(Fieldname.get(i));
            System.out.println(DataType.get(i));
            System.out.println("________________");
        }

        StringBuilder sql=new StringBuilder();
        sql.append("select * from "+tableName+" where "+Fieldname.get(0)+" = ?");

        Connection connection=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            connection = DBUtil.getConn();
            ps = connection.prepareStatement(sql.toString());
            ps.setInt(1,selectedStudentId);
            rs = ps.executeQuery();
            System.out.println("sql:"+sql);
            int l=DataType.size();

            Vector<Object> attributes=new Vector<>();
            while (rs.next()){
                for (int i = 0; i < l ; i++) {
                    if(DataType.get(i).equals("INT")||DataType.get(i).equals("INT UNSIGNED")){
                        int temp= rs.getInt(Fieldname.get(i));
                        System.out.println("temp:"+temp);
                        attributes.add(temp);
                    }else if(DataType.get(i).equals("VARCHAR")){
                        String temp= rs.getString(Fieldname.get(i));
                        System.out.println("temp:"+temp);

                        attributes.add(temp);
                    } else if (DataType.get(i).equals("DATE")) {
                        Date temp= rs.getDate(Fieldname.get(i));
                        System.out.println("temp:"+temp);

                        attributes.add(temp);
                    } else if (DataType.get(i).equals("DECIMAL")) {
                        Double temp= rs.getDouble(Fieldname.get(i));
                        System.out.println("temp:"+temp);

                        attributes.add(temp);
                    }
                }
            }
            return attributes;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeRs(rs);
            DBUtil.closePS(ps);
            DBUtil.closeConn(connection);
        }
        return null;
    }
    //
    @Override //回显函数

    //sql:select * from Personnel where PersonnelID = ?
    //sql:select * from Personnel where PersonnelID = ?

    public Personnel getById(int selectedStudentId, String tableName) {

        Vector<String> Fieldname=new Vector<>();
        Vector<String> DataType=new Vector<>();
        try (Connection connection = DBUtil.getConn()) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet columns = metaData.getColumns(null, null, tableName, null);
            int i=0;
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                Fieldname.add(columnName);
                String columnType = columns.getString("TYPE_NAME");
                DataType.add(columnType);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("selectedStudentId:"+selectedStudentId);
        for (int i = 0; i < Fieldname.size(); i++) {
            System.out.println(Fieldname.get(i));
            System.out.println(DataType.get(i));
            System.out.println("________________");
        }

        StringBuilder sql=new StringBuilder();
        sql.append("select * from "+tableName+" where PersonnelID = ?");
        Connection connection=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Personnel personnel=new Personnel();
        try {
            connection = DBUtil.getConn();
            ps = connection.prepareStatement(sql.toString());
            ps.setInt(1,selectedStudentId);
            System.out.println("sql:"+sql);
            rs = ps.executeQuery();
            while (rs.next()){
                int personnelID=rs.getInt("PersonnelID");
                String fullName=rs.getString("FullName");
                String gender=rs.getString("Gender");
                String ethnicity=rs.getString("ethnicity");
                int departmentID=rs.getInt("departmentID");
                String title=rs.getString("title");
                Date birthDate=rs.getDate("birthDate");
                String educationLevel=rs.getString("educationLevel");
                double baseSalary=rs.getDouble("baseSalary");
                double positionSalary=rs.getDouble("positionSalary");
                double positionAllowance=rs.getDouble("positionAllowance");
                Date hireDate=rs.getDate("hireDate");
                personnel.setPersonnelID(personnelID);
                personnel.setFullName(fullName);
                personnel.setGender(gender);
                personnel.setEthnicity(ethnicity);
                personnel.setDepartmentID(departmentID);
                personnel.setTitle(title);
                personnel.setBirthDate(birthDate);
                personnel.setEducationLevel(educationLevel);
                personnel.setBaseSalary(baseSalary);
                personnel.setPositionSalary(positionSalary);
                personnel.setPositionAllowance(positionAllowance);
                personnel.setHireDate(hireDate);
            }
            if(personnel!=null){
                System.out.println("!null");
            }
            return personnel;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeRs(rs);
            DBUtil.closePS(ps);
            DBUtil.closeConn(connection);
        }
        return null;
    }

    @Override
    public Achievement getByIdAchievement(int selectedStudentId, String tablename) {
        return null;
    }

    @Override
    public Department getByIdDepartment(int selectedStudentId, String tablename) {
        return null;
    }

    @Override
    public Project getByIdProject(int selectedStudentId, String tablename) {
        return null;
    }

    @Override
    public ProjectAwardForm getByIdProjectAwardForm(int selectedStudentId, String tablename) {
        return null;
    }

    @Override
    public ProjectPersonnelTable getByIdProjectPersonnelTable(int selectedStudentId, String tablename) {
        return null;
    }


    @Override
    public boolean updata(int key,Vector<Object> attributes,String tablesName) {
//        sql.append("UPDATE Achievement SET Category=?,AchievementLevel=?,Grade=?WHERE AchievementID=?;");
        //准备工作：
        Vector<String> Fieldname=new Vector<>();
        Vector<String> DataType=new Vector<>();
        try (Connection connection = DBUtil.getConn()) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet columns = metaData.getColumns(null, null, tablesName, null);
            int i=0;
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                Fieldname.add(columnName);
                String columnType = columns.getString("TYPE_NAME");
                DataType.add(columnType);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        StringBuilder sql=new StringBuilder();
        sql.append("UPDATE ");
        sql.append(tablesName);
        sql.append(" SET ");
        int length=Fieldname.size();
        int s=0,j=0;
        if(tablesName.equals("projectpersonneltable")){
            s=1;
        }
        for(;s<length;s++){
            if(s<length-1)
                sql.append(Fieldname.get(s)+" = ? , ");
            else {
                sql.append(Fieldname.get(s)+" = ?");

            }
        }
        System.out.println("拼接"+sql);
        sql.append(" WHERE "+Fieldname.get(0)+"= ?;");
        System.out.println("拼接"+sql);
        Connection connection=null;
        PreparedStatement ps=null;

        try{
            connection=DBUtil.getConn();
            ps = connection.prepareStatement(sql.toString());
            int l=DataType.size();
            if(tablesName.equals("projectpersonneltable")){
                ps.setInt(1,(Integer) attributes.get(0));
                ps.setInt(2,(Integer) attributes.get(1));
                ps.setString(3,(String) attributes.get(2));
                ps.setDate(4,(Date) attributes.get(3));
                ps.setDate(5,(Date) attributes.get(4));
                ps.setInt(6,key);
            }
            else {
                for (int i = 0; i < l ; i++) {
                    if(DataType.get(i).equals("INT")||DataType.get(i).equals("INT UNSIGNED")){
                        ps.setInt(i+1, (Integer) attributes.get(i));
                    }else if(DataType.get(i).equals("VARCHAR")){
                        ps.setString(i+1,(String)attributes.get(i));
                    } else if (DataType.get(i).equals("DATE")) {
                        ps.setDate(i+1,(Date)attributes.get(i));
                    } else if (DataType.get(i).equals("DECIMAL")) {
                        ps.setDouble(i+1,(Double)attributes.get(i));
                    }
                }
                ps.setInt(l+1,key);
            }
            return ps.executeUpdate()==1;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePS(ps);
            DBUtil.closeConn(connection);
        }
        return false;
    }

    //删除是吧
    @Override
    public boolean delete(String[] selectedStudentIds,String tableName) {
        StringBuilder sql=new StringBuilder();


        //准备工作：
        Vector<String> Fieldname=new Vector<>();
        Vector<String> DataType=new Vector<>();
        try (Connection connection = DBUtil.getConn()) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet columns = metaData.getColumns(null, null, tableName, null);
            int i=0;
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                Fieldname.add(columnName);
                String columnType = columns.getString("TYPE_NAME");
                DataType.add(columnType);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        sql.append("DELETE FROM "+tableName+" WHERE "+Fieldname.get(0)+" IN (");
        int length=selectedStudentIds.length;
        for (int i = 0; i < length; i++) {
            if(i==length-1){
                sql.append("?");
            }else {
                sql.append("?,");
            }
        }
        sql.append(")");
        Connection connection=null;
        PreparedStatement ps=null;
        try{
            connection=DBUtil.getConn();
            ps = connection.prepareStatement(sql.toString());
            for (int i = 0; i < length; i++) {

                //设置参数
                if(DataType.get(i).equals("INT")||DataType.get(i).equals("INT UNSIGNED")){
                    ps.setInt(i+1, Integer.parseInt(selectedStudentIds[i]));
                }else if(DataType.get(i).equals("VARCHAR")){
                    ps.setString(i+1,selectedStudentIds[i]);
                }
            }
            int l=ps.executeUpdate();
            if(tableName.equals("projectpersonneltable")){
                try {
                    Statement statement = connection.createStatement();
                    String sql1 = "ALTER TABLE projectpersonneltable DROP ProjectPersonnelID;";
                    statement.execute(sql1);
                    String sql2 = "ALTER TABLE projectpersonneltable ADD ProjectPersonnelID INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;";
                    statement.execute(sql2);
                    // 4. 关闭Statement和Connection对象
                    statement.close();
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            return l==length;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePS(ps);
            DBUtil.closeConn(connection);
        }
        //
        return false;
    }
}
